﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Clinica_Frba.Listados_Estadisticos.queries
{
    class EspecialidadesConMasCancelaciones : Query
    {
        private static string EspecialidadesConMasCancelacionesQuery1 = " select TOP 5 E.DESC_ESP as Especialidad, COUNT(*) AS Cancelaciones, DATEPART ( MONTH , C.Fecha ) AS Mes " +
             " from CANCELACION C, TURNO T, PROFESIONAL P, PROFESIONAL_ESPECIALIDAD PE, ESPECIALIDAD E " +
             " WHERE " +
             " C.ID_TURNO = T.ID_TURNO " +
             " AND T.ID_PROF = P.ID_PROF " +
             " AND P.ID_PROF = PE.ID_PROF " +
             " AND PE.ID_ESP = E.ID_ESP " +
             " and DATEPART ( YEAR , C.Fecha ) in (2013, 2014) " +
             " and DATEPART ( MONTH , C.Fecha ) <= 6 " +
             " GROUP BY E.DESC_ESP, DATEPART ( MONTH , C.Fecha )   " +
             " ORDER BY Cancelaciones DESC, Mes ASC ";

        private static string EspecialidadesConMasCancelacionesQuery2 = " select TOP 5 E.DESC_ESP as Especialidad, COUNT(*) AS Cancelaciones, DATEPART ( MONTH , C.Fecha ) AS Mes " +
            " from CANCELACION C, TURNO T, PROFESIONAL P, PROFESIONAL_ESPECIALIDAD PE, ESPECIALIDAD E " +
            " WHERE " +
            " C.ID_TURNO = T.ID_TURNO " +
            " AND T.ID_PROF = P.ID_PROF " +
            " AND P.ID_PROF = PE.ID_PROF " +
            " AND PE.ID_ESP = E.ID_ESP " +
            " and DATEPART ( YEAR , C.Fecha ) in (2013, 2014) " +
            " and DATEPART ( MONTH , C.Fecha ) > 6 " +
            " GROUP BY E.DESC_ESP, DATEPART ( MONTH , C.Fecha )   " +
            " ORDER BY Cancelaciones DESC, Mes ASC ";

        public EspecialidadesConMasCancelaciones()
            : base
                ("Especialidades con mas cancelaciones", EspecialidadesConMasCancelacionesQuery1, EspecialidadesConMasCancelacionesQuery2)
        {
        }

        override public String SqlQuery(int semestre, int year)
        {
            if (semestre == 1)
            {
                return " select TOP 5 E.DESC_ESP as Especialidad, SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) BETWEEN 1 AND 6 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) AS Cancelaciones, " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 1 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_1, " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 2 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_2, " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 3 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_3, " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 4 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_4, " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 5 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_5, " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 6 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_6 " +
" from ESPECIALIDAD E LEFT JOIN PROFESIONAL_ESPECIALIDAD PE ON E.ID_ESP = PE.ID_ESP LEFT JOIN PROFESIONAL P ON PE.ID_PROF = P.ID_PROF LEFT JOIN TURNO T ON P.ID_PROF = T.ID_PROF LEFT JOIN CANCELACION C ON T.ID_TURNO = C.ID_TURNO " +
//" WHERE " +
//" DATEPART ( YEAR , C.Fecha ) = " + year + 
//" and DATEPART ( MONTH , C.Fecha ) <= 6 " +
" GROUP BY E.DESC_ESP  " +
" ORDER BY Cancelaciones DESC ";
            }
            else
            {
                return " select TOP 5 E.DESC_ESP as Especialidad, SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) BETWEEN 7 AND 12 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) AS Cancelaciones,  " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 7 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_7, " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 8 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_8, " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 9 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_9, " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 10 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_10, " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 11 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_11, " +
" SUM(CASE WHEN ID_CANCEL IS NOT NULL and DATEPART ( MONTH , C.Fecha ) = 12 AND DATEPART ( YEAR , C.Fecha ) = " + year + " THEN 1 ELSE 0 END) as mes_12 " +
" from ESPECIALIDAD E LEFT JOIN PROFESIONAL_ESPECIALIDAD PE ON E.ID_ESP = PE.ID_ESP LEFT JOIN PROFESIONAL P ON PE.ID_PROF = P.ID_PROF LEFT JOIN TURNO T ON P.ID_PROF = T.ID_PROF LEFT JOIN CANCELACION C ON T.ID_TURNO = C.ID_TURNO " +
//" WHERE " +
//" DATEPART ( YEAR , C.Fecha ) = " + year +
//" and DATEPART ( MONTH , C.Fecha ) > 6 " +
" GROUP BY E.DESC_ESP  " +
" ORDER BY Cancelaciones DESC";
            }
        }
    }
}
